﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace Salon
{
    class ClientService
    {
        public ClientService() { }

        public Client saveClient(Client client)
        {
            String insertQuery = "INSERT INTO Client(name,country,city,street,zip,flat_number) VALUES(@name,@country,@city,@street,@zip,@flat_number)";

            String updateQuery = "UPDATE Client SET name=@name,country=@country,city=@city,street=@street,zip=@zip,flat_number=@flat_number,active=@active WHERE id=@id";

            SqlCommand cmd = SqlService.getInstance().getCommand( (client.Id < 1) ? insertQuery : updateQuery );
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@name", client.Name);
            cmd.Parameters.AddWithValue("@country", client.Country);
            cmd.Parameters.AddWithValue("@city", client.City);
            cmd.Parameters.AddWithValue("@street", client.Street);
            cmd.Parameters.AddWithValue("@zip", client.Zip);
            cmd.Parameters.AddWithValue("@flat_number", client.FlatNumber);
            cmd.Parameters.AddWithValue("@active", 1);
            if ( client.Id > 0 )
                cmd.Parameters.AddWithValue("@id", client.Id);
            cmd.ExecuteNonQuery();

            SqlService.getInstance().getConnection().Close();

            return client;
        }

        //przy usuwaniu ustawiamy flagę active na false
        public void deleteClient(Client Client)
        {
            String query = "DELETE FROM Client WHERE id=@id";
            SqlCommand cmd = SqlService.getInstance().getCommand(query);
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@id", Client.Id);
            cmd.ExecuteNonQuery();
        }

        //sprawdza czy dany klient już istnieje i zwraca id
        public int checkIfNameExists(String client_name)
        {
            String query = "Select id FROM Client WHERE name=@clien_name";
            SqlCommand cmd = SqlService.getInstance().getCommand(query);
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@clien_name", client_name);
            String id = (String)cmd.ExecuteScalar();

            if (id != null) return Convert.ToInt32(id);
            return 0;
        }

        public Client getClientById(int id)
        {
            String query = "SELECT * from Client WHERE id=@id";
            SqlCommand cmd = SqlService.getInstance().getCommand(query);
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@id", id);
            SqlDataReader rdr = cmd.ExecuteReader();
            if (!rdr.HasRows)
                return null;
            rdr.Read();

            Client Client = new Client();
            Client.Id = rdr.GetInt32(rdr.GetOrdinal("id"));
            Client.Name = rdr.GetString(rdr.GetOrdinal("name"));
            Client.Country = rdr.GetString(rdr.GetOrdinal("country"));
            Client.City = rdr.GetString(rdr.GetOrdinal("city"));
            Client.Street = rdr.GetString(rdr.GetOrdinal("street"));
            Client.Zip = rdr.GetString(rdr.GetOrdinal("zip"));
            Client.FlatNumber = rdr.GetString(rdr.GetOrdinal("flat_number"));

            rdr.Close();

            return Client;
        }

        /// <summary>
        /// Zwraca listę obiektów typu Client na podstawie listy kontrolek filtrujących
        /// </summary>
        /// <param name="filterList"></param>
        /// <returns></returns>
        public IList<Client> getClients(IList<IFilter> filterList)
        {
            IList<Client> Clients = new List<Client>();
            String query = "SELECT * FROM Client WHERE id > 0 ";
            query += Global.getConditionString(filterList);

            SqlDataReader reader = SqlService.getInstance().getCommand(query).ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    Client Client = new Client();
                    Client.Id = reader.GetInt32(reader.GetOrdinal("id"));
                    Client.Name = reader.GetString(reader.GetOrdinal("name"));
                    Client.Country = reader.GetString(reader.GetOrdinal("country"));
                    Client.City = reader.GetString(reader.GetOrdinal("city"));
                    Client.Street = reader.GetString(reader.GetOrdinal("street"));
                    Client.Zip = reader.GetString(reader.GetOrdinal("zip"));
                    Client.FlatNumber = reader.GetString(reader.GetOrdinal("flat_number"));

                    Clients.Add(Client);
                }
            }

            reader.Close();

            return Clients;
        }
    }
}
